{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3cba8a51",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e7d45c9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\3技术研究\\主题年份计算\\3文档属于主题的概率.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ea006777",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(69273, 9)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>PN 专利号</th>\n",
       "      <th>TI 文献标题</th>\n",
       "      <th>AD 申请详细信息</th>\n",
       "      <th>预处理文本</th>\n",
       "      <th>预处理文本2</th>\n",
       "      <th>Document</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>patent5534</td>\n",
       "      <td>US2015071737-A1;  US10424498-B2</td>\n",
       "      <td>Transport apparatus for vacuum transport syste...</td>\n",
       "      <td>US2015071737-A1    US480803    09 Sep 2014;   ...</td>\n",
       "      <td>transport apparatus vacuum transport system ar...</td>\n",
       "      <td>transport apparatus vacuum transport system ar...</td>\n",
       "      <td>transport apparatus vacuum transport system ar...</td>\n",
       "      <td>-1</td>\n",
       "      <td>0.586962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>patent31677</td>\n",
       "      <td>CN107498781-A;  CN207290719-U;  CN107498781-B</td>\n",
       "      <td>Automatic injection molding machine feeding   ...</td>\n",
       "      <td>CN107498781-A    CN10664563    04 Aug 2017;   ...</td>\n",
       "      <td>automatic injection mold machine feeding syste...</td>\n",
       "      <td>automatic injection mold machine feed system c...</td>\n",
       "      <td>automatic injection mold machine feed system c...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.945716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>patent83440</td>\n",
       "      <td>CN108029284-A;  CN207869642-U</td>\n",
       "      <td>Fully-automatic single-row planting robot, has...</td>\n",
       "      <td>CN108029284-A    CN10027388    11 Jan 2018;   ...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.930100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>patent10376</td>\n",
       "      <td>CN104785960-A;  CN104785960-B</td>\n",
       "      <td>Grid structure type robot welding device has r...</td>\n",
       "      <td>CN104785960-A    CN10150108    31 Mar 2015;   ...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.915050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>patent12420</td>\n",
       "      <td>WO2017043583-A1;  TW201718203-A;  JP2017539221...</td>\n",
       "      <td>Robot apparatus for use in industrial applicat...</td>\n",
       "      <td>WO2017043583-A1    WOJP076473    08 Sep 2016; ...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.783842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            编号                                             PN 专利号  \\\n",
       "0   patent5534                    US2015071737-A1;  US10424498-B2   \n",
       "1  patent31677      CN107498781-A;  CN207290719-U;  CN107498781-B   \n",
       "2  patent83440                      CN108029284-A;  CN207869642-U   \n",
       "3  patent10376                      CN104785960-A;  CN104785960-B   \n",
       "4  patent12420  WO2017043583-A1;  TW201718203-A;  JP2017539221...   \n",
       "\n",
       "                                             TI 文献标题  \\\n",
       "0  Transport apparatus for vacuum transport syste...   \n",
       "1  Automatic injection molding machine feeding   ...   \n",
       "2  Fully-automatic single-row planting robot, has...   \n",
       "3  Grid structure type robot welding device has r...   \n",
       "4  Robot apparatus for use in industrial applicat...   \n",
       "\n",
       "                                           AD 申请详细信息  \\\n",
       "0  US2015071737-A1    US480803    09 Sep 2014;   ...   \n",
       "1  CN107498781-A    CN10664563    04 Aug 2017;   ...   \n",
       "2  CN108029284-A    CN10027388    11 Jan 2018;   ...   \n",
       "3  CN104785960-A    CN10150108    31 Mar 2015;   ...   \n",
       "4  WO2017043583-A1    WOJP076473    08 Sep 2016; ...   \n",
       "\n",
       "                                               预处理文本  \\\n",
       "0  transport apparatus vacuum transport system ar...   \n",
       "1  automatic injection mold machine feeding syste...   \n",
       "2  fully-automatic single-row planting robot rota...   \n",
       "3  grid structure type robot weld device robot pr...   \n",
       "4  robot apparatus industrial application operati...   \n",
       "\n",
       "                                              预处理文本2  \\\n",
       "0  transport apparatus vacuum transport system ar...   \n",
       "1  automatic injection mold machine feed system c...   \n",
       "2  fully-automatic single-row planting robot rota...   \n",
       "3  grid structure type robot weld device robot pr...   \n",
       "4  robot apparatus industrial application operati...   \n",
       "\n",
       "                                            Document  Topic  Probability  \n",
       "0  transport apparatus vacuum transport system ar...     -1     0.586962  \n",
       "1  automatic injection mold machine feed system c...      0     0.945716  \n",
       "2  fully-automatic single-row planting robot rota...      0     0.930100  \n",
       "3  grid structure type robot weld device robot pr...      0     0.915050  \n",
       "4  robot apparatus industrial application operati...      0     0.783842  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c41a19dd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████| 69273/69273 [00:00<00:00, 148769.57it/s]\n"
     ]
    }
   ],
   "source": [
    "from tqdm import tqdm\n",
    "\n",
    "year_ls = []\n",
    "for i in tqdm(range(0,len(df['AD 申请详细信息'].values))):\n",
    "    ii = df['AD 申请详细信息'].values[i]\n",
    "    year_ls.append(int(ii.split(';')[0][-4:]))\n",
    "\n",
    "df['申请年'] = year_ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c00be32e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.to_excel(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\3技术研究\\主题年份计算\\4抽取专利申请年.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7c362f56",
   "metadata": {},
   "outputs": [],
   "source": [
    "# excel删除后重新读取\n",
    "df = pd.read_excel(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\3技术研究\\主题年份计算\\4抽取专利申请年.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6fdbdaf6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(39481, 10)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>编号</th>\n",
       "      <th>PN 专利号</th>\n",
       "      <th>TI 文献标题</th>\n",
       "      <th>AD 申请详细信息</th>\n",
       "      <th>预处理文本</th>\n",
       "      <th>预处理文本2</th>\n",
       "      <th>Document</th>\n",
       "      <th>Topic</th>\n",
       "      <th>Probability</th>\n",
       "      <th>申请年</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>patent31677</td>\n",
       "      <td>CN107498781-A;  CN207290719-U;  CN107498781-B</td>\n",
       "      <td>Automatic injection molding machine feeding   ...</td>\n",
       "      <td>CN107498781-A    CN10664563    04 Aug 2017;   ...</td>\n",
       "      <td>automatic injection mold machine feeding syste...</td>\n",
       "      <td>automatic injection mold machine feed system c...</td>\n",
       "      <td>automatic injection mold machine feed system c...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.945716</td>\n",
       "      <td>2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>patent83440</td>\n",
       "      <td>CN108029284-A;  CN207869642-U</td>\n",
       "      <td>Fully-automatic single-row planting robot, has...</td>\n",
       "      <td>CN108029284-A    CN10027388    11 Jan 2018;   ...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>fully-automatic single-row planting robot rota...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.930100</td>\n",
       "      <td>2018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>patent10376</td>\n",
       "      <td>CN104785960-A;  CN104785960-B</td>\n",
       "      <td>Grid structure type robot welding device has r...</td>\n",
       "      <td>CN104785960-A    CN10150108    31 Mar 2015;   ...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>grid structure type robot weld device robot pr...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.915050</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>patent12420</td>\n",
       "      <td>WO2017043583-A1;  TW201718203-A;  JP2017539221...</td>\n",
       "      <td>Robot apparatus for use in industrial applicat...</td>\n",
       "      <td>WO2017043583-A1    WOJP076473    08 Sep 2016; ...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>robot apparatus industrial application operati...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.783842</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>patent8449</td>\n",
       "      <td>JP2015013298-A;  JP6159599-B2</td>\n",
       "      <td>Laser welder used for welding product of weldi...</td>\n",
       "      <td>JP2015013298-A    JP140295    04 Jul 2013;   J...</td>\n",
       "      <td>laser welder weld product weld object laser be...</td>\n",
       "      <td>laser welder weld product weld object laser be...</td>\n",
       "      <td>laser welder weld product weld object laser be...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.916185</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            编号                                             PN 专利号  \\\n",
       "0  patent31677      CN107498781-A;  CN207290719-U;  CN107498781-B   \n",
       "1  patent83440                      CN108029284-A;  CN207869642-U   \n",
       "2  patent10376                      CN104785960-A;  CN104785960-B   \n",
       "3  patent12420  WO2017043583-A1;  TW201718203-A;  JP2017539221...   \n",
       "4   patent8449                      JP2015013298-A;  JP6159599-B2   \n",
       "\n",
       "                                             TI 文献标题  \\\n",
       "0  Automatic injection molding machine feeding   ...   \n",
       "1  Fully-automatic single-row planting robot, has...   \n",
       "2  Grid structure type robot welding device has r...   \n",
       "3  Robot apparatus for use in industrial applicat...   \n",
       "4  Laser welder used for welding product of weldi...   \n",
       "\n",
       "                                           AD 申请详细信息  \\\n",
       "0  CN107498781-A    CN10664563    04 Aug 2017;   ...   \n",
       "1  CN108029284-A    CN10027388    11 Jan 2018;   ...   \n",
       "2  CN104785960-A    CN10150108    31 Mar 2015;   ...   \n",
       "3  WO2017043583-A1    WOJP076473    08 Sep 2016; ...   \n",
       "4  JP2015013298-A    JP140295    04 Jul 2013;   J...   \n",
       "\n",
       "                                               预处理文本  \\\n",
       "0  automatic injection mold machine feeding syste...   \n",
       "1  fully-automatic single-row planting robot rota...   \n",
       "2  grid structure type robot weld device robot pr...   \n",
       "3  robot apparatus industrial application operati...   \n",
       "4  laser welder weld product weld object laser be...   \n",
       "\n",
       "                                              预处理文本2  \\\n",
       "0  automatic injection mold machine feed system c...   \n",
       "1  fully-automatic single-row planting robot rota...   \n",
       "2  grid structure type robot weld device robot pr...   \n",
       "3  robot apparatus industrial application operati...   \n",
       "4  laser welder weld product weld object laser be...   \n",
       "\n",
       "                                            Document  Topic  Probability   申请年  \n",
       "0  automatic injection mold machine feed system c...      0     0.945716  2017  \n",
       "1  fully-automatic single-row planting robot rota...      0     0.930100  2018  \n",
       "2  grid structure type robot weld device robot pr...      0     0.915050  2015  \n",
       "3  robot apparatus industrial application operati...      0     0.783842  2016  \n",
       "4  laser welder weld product weld object laser be...      0     0.916185  2013  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "64651003",
   "metadata": {},
   "outputs": [],
   "source": [
    "def computer_power_year(df, topic):\n",
    "    # 获取相应主题\n",
    "    topic_df = df[df['Topic'] == topic]\n",
    "    # 先算probability占比，然后占比乘年份求和\n",
    "    power_year_ls = []\n",
    "    for year,probability in zip(topic_df['申请年'].values,topic_df['Probability'].values):\n",
    "        power = probability/topic_df['Probability'].sum()\n",
    "        power_year = power * year\n",
    "        power_year_ls.append(power_year)\n",
    "    finally_year = sum(power_year_ls)\n",
    "    # 合并所有归属于当前主题的文档\n",
    "    text = topic_df['Document'].values\n",
    "    \n",
    "    return finally_year, text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cacdd874",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "adecc4bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['主题'] = [i for i in range(0,len(df['Topic'].unique()))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "abf2c974",
   "metadata": {},
   "outputs": [],
   "source": [
    "ls_year, ls_text = [], []\n",
    "for i in range(0,len(df['Topic'].unique())):\n",
    "    finally_year, text = computer_power_year(df, i)\n",
    "    ls_year.append(round(finally_year,3))\n",
    "    ls_text.append(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "865345da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存主题对应的文档\n",
    "topic_doc = {}\n",
    "for i in range(len(ls_text)):\n",
    "    topic_doc[data['主题'].values[i]] = ls_text[i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "77ca5de8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pickle\n",
    "with open(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\4主题关联\\1技术研究主题-文档内容.pkl','wb') as f:\n",
    "    pickle.dump(topic_doc,f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ad74dd2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5f7449b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "dd02ecc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['年份'] = ls_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "16ccca51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>主题</th>\n",
       "      <th>年份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2016.427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2015.463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2017.285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2020.123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2016.927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2018.581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2018.422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>2018.670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2017.467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>2013.711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>2017.672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>2016.133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>2017.819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>2016.737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>2018.024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>2014.883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>2019.263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>2013.126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>2012.061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>2017.568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>2014.587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>2016.004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>2018.251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>2013.247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>2014.067</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>2018.149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>2015.701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>2015.581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>2014.269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>2017.256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30</td>\n",
       "      <td>2013.277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>2011.698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>2018.321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>2009.560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34</td>\n",
       "      <td>2017.007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35</td>\n",
       "      <td>2014.258</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    主题        年份\n",
       "0    0  2016.427\n",
       "1    1  2015.463\n",
       "2    2  2017.285\n",
       "3    3  2020.123\n",
       "4    4  2016.927\n",
       "5    5  2018.581\n",
       "6    6  2018.422\n",
       "7    7  2018.670\n",
       "8    8  2017.467\n",
       "9    9  2013.711\n",
       "10  10  2017.672\n",
       "11  11  2016.133\n",
       "12  12  2017.819\n",
       "13  13  2016.737\n",
       "14  14  2018.024\n",
       "15  15  2014.883\n",
       "16  16  2019.263\n",
       "17  17  2013.126\n",
       "18  18  2012.061\n",
       "19  19  2017.568\n",
       "20  20  2014.587\n",
       "21  21  2016.004\n",
       "22  22  2018.251\n",
       "23  23  2013.247\n",
       "24  24  2014.067\n",
       "25  25  2018.149\n",
       "26  26  2015.701\n",
       "27  27  2015.581\n",
       "28  28  2014.269\n",
       "29  29  2017.256\n",
       "30  30  2013.277\n",
       "31  31  2011.698\n",
       "32  32  2018.321\n",
       "33  33  2009.560\n",
       "34  34  2017.007\n",
       "35  35  2014.258"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "efc55af3",
   "metadata": {},
   "outputs": [],
   "source": [
    "topic_words = pd.read_excel(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\3技术研究\\3主题-主题词.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "161b0158",
   "metadata": {},
   "outputs": [],
   "source": [
    "topic_words_ls = []\n",
    "for i in range(0,len(df['Topic'].unique())):\n",
    "    words_ls = topic_words.iloc[:,i+1].values\n",
    "    words = '@'.join(words_ls)\n",
    "    topic_words_ls.append(words)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9f0dc0fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "data['主题词'] = topic_words_ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "aba0db0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>主题</th>\n",
       "      <th>年份</th>\n",
       "      <th>主题词</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2016.427</td>\n",
       "      <td>weld@robot@device@connect@provide@mechanism@ar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2015.463</td>\n",
       "      <td>piezoelectric@ultrasonic@layer@cable@wire@cond...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2017.285</td>\n",
       "      <td>food@cook@cooking@dish@ingredient@kitchen@pot@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2020.123</td>\n",
       "      <td>sterilize@disinfection@liquid@spray@disinfecta...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2016.927</td>\n",
       "      <td>medicine@dispense@drug@bottle@medication@stora...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2018.581</td>\n",
       "      <td>bottle@tea@beverage@wine@cup@coffee@device@con...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2018.422</td>\n",
       "      <td>substation@transformer@inspection@system@modul...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>2018.670</td>\n",
       "      <td>mop@clean@water@tank@floor@sweep@assembly@clot...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>2017.467</td>\n",
       "      <td>fish@fin@bionic@tail@pectoral@fishing@control@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>2013.711</td>\n",
       "      <td>temperature@heat@sensor@unit@control@cool@meas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>2017.672</td>\n",
       "      <td>ice@snow@deicing@remove@deice@transmission@thi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>2016.133</td>\n",
       "      <td>tire@vulcanize@tyre@wheel@unload@arm@beam@mach...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>2017.819</td>\n",
       "      <td>needle@puncture@blood@assembly@mechanism@devic...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>2016.737</td>\n",
       "      <td>massage@acupuncture@chinese@upper@human@parall...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>2018.024</td>\n",
       "      <td>book@library@bookshelf@management@mechanism@mo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>2014.883</td>\n",
       "      <td>eye@eyeball@eyelid@eyebrow@head@expression@fac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>2019.263</td>\n",
       "      <td>yarn@bobbin@twist@mechanism@feed@device@creel@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>2013.126</td>\n",
       "      <td>simulation@model@virtual@simulate@display@prog...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>2012.061</td>\n",
       "      <td>milk@teat@animal@cup@cow@milking@stall@dairy@p...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>2017.568</td>\n",
       "      <td>dispense@glue@dispenser@gel@material@tape@cont...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>2014.587</td>\n",
       "      <td>xray@detector@source@image@imaging@ray@carm@sy...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>2016.004</td>\n",
       "      <td>spherical@shell@drive@assembly@head@shield@rob...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>2018.251</td>\n",
       "      <td>glaze@spray@multinozzle@blank@ceramic@glazing@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>2013.247</td>\n",
       "      <td>cash@money@transaction@register@currency@physi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>2014.067</td>\n",
       "      <td>abnormality@apparatus@determination@detect@abn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>2018.149</td>\n",
       "      <td>shoe@glue@spray@mold@vamp@sole@die@automatic@m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>2015.701</td>\n",
       "      <td>uterine@uterus@cup@manipulator@vagina@colpotom...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>2015.581</td>\n",
       "      <td>guidance@user@destination@guide@ticket@unit@ro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>2014.269</td>\n",
       "      <td>pallet@palletizing@product@palletizer@package@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>2017.256</td>\n",
       "      <td>brain@signal@electroencephalogram@wave@brainco...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30</td>\n",
       "      <td>2013.277</td>\n",
       "      <td>hanger@panel@beam@clothes@stock@circulate@crad...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>2011.698</td>\n",
       "      <td>wafer@polish@chemical@polishing@semiconductor@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>2018.321</td>\n",
       "      <td>humidity@sensor@temperature@dry@air@clothes@en...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>2009.560</td>\n",
       "      <td>reticle@pod@load@stocker@shelf@transfer@exposu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34</td>\n",
       "      <td>2017.007</td>\n",
       "      <td>adhesive@tape@cut@shade@glue@press@easytotear@...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35</td>\n",
       "      <td>2014.258</td>\n",
       "      <td>culture@cell@incubator@vessel@container@liquid...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    主题        年份                                                主题词\n",
       "0    0  2016.427  weld@robot@device@connect@provide@mechanism@ar...\n",
       "1    1  2015.463  piezoelectric@ultrasonic@layer@cable@wire@cond...\n",
       "2    2  2017.285  food@cook@cooking@dish@ingredient@kitchen@pot@...\n",
       "3    3  2020.123  sterilize@disinfection@liquid@spray@disinfecta...\n",
       "4    4  2016.927  medicine@dispense@drug@bottle@medication@stora...\n",
       "5    5  2018.581  bottle@tea@beverage@wine@cup@coffee@device@con...\n",
       "6    6  2018.422  substation@transformer@inspection@system@modul...\n",
       "7    7  2018.670  mop@clean@water@tank@floor@sweep@assembly@clot...\n",
       "8    8  2017.467  fish@fin@bionic@tail@pectoral@fishing@control@...\n",
       "9    9  2013.711  temperature@heat@sensor@unit@control@cool@meas...\n",
       "10  10  2017.672  ice@snow@deicing@remove@deice@transmission@thi...\n",
       "11  11  2016.133  tire@vulcanize@tyre@wheel@unload@arm@beam@mach...\n",
       "12  12  2017.819  needle@puncture@blood@assembly@mechanism@devic...\n",
       "13  13  2016.737  massage@acupuncture@chinese@upper@human@parall...\n",
       "14  14  2018.024  book@library@bookshelf@management@mechanism@mo...\n",
       "15  15  2014.883  eye@eyeball@eyelid@eyebrow@head@expression@fac...\n",
       "16  16  2019.263  yarn@bobbin@twist@mechanism@feed@device@creel@...\n",
       "17  17  2013.126  simulation@model@virtual@simulate@display@prog...\n",
       "18  18  2012.061  milk@teat@animal@cup@cow@milking@stall@dairy@p...\n",
       "19  19  2017.568  dispense@glue@dispenser@gel@material@tape@cont...\n",
       "20  20  2014.587  xray@detector@source@image@imaging@ray@carm@sy...\n",
       "21  21  2016.004  spherical@shell@drive@assembly@head@shield@rob...\n",
       "22  22  2018.251  glaze@spray@multinozzle@blank@ceramic@glazing@...\n",
       "23  23  2013.247  cash@money@transaction@register@currency@physi...\n",
       "24  24  2014.067  abnormality@apparatus@determination@detect@abn...\n",
       "25  25  2018.149  shoe@glue@spray@mold@vamp@sole@die@automatic@m...\n",
       "26  26  2015.701  uterine@uterus@cup@manipulator@vagina@colpotom...\n",
       "27  27  2015.581  guidance@user@destination@guide@ticket@unit@ro...\n",
       "28  28  2014.269  pallet@palletizing@product@palletizer@package@...\n",
       "29  29  2017.256  brain@signal@electroencephalogram@wave@brainco...\n",
       "30  30  2013.277  hanger@panel@beam@clothes@stock@circulate@crad...\n",
       "31  31  2011.698  wafer@polish@chemical@polishing@semiconductor@...\n",
       "32  32  2018.321  humidity@sensor@temperature@dry@air@clothes@en...\n",
       "33  33  2009.560  reticle@pod@load@stocker@shelf@transfer@exposu...\n",
       "34  34  2017.007  adhesive@tape@cut@shade@glue@press@easytotear@...\n",
       "35  35  2014.258  culture@cell@incubator@vessel@container@liquid..."
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "726780dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel(r'D:\\jupyter\\DK\\DK数据\\2数据处理\\3聚类\\3技术研究\\主题年份计算\\5技术研究-主题年份及主题词.xlsx',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e80c51ba",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0c02134",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
